﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Autenticazione.BusinessObject
{
    public class Fights
    {
        public string EMail;
        public long Id;
        public string Iscrizioni;
        private int IdEvento;
        
        public Fights()
        {
            this.IdEvento = int.Parse(System.Configuration.ConfigurationManager.AppSettings["IdEvento"]);
        }

        public Fights(string email, long id, string iscrizioni)
        {
            this.EMail = email;
            this.Id = id;
            this.Iscrizioni = iscrizioni;
            this.IdEvento = int.Parse(System.Configuration.ConfigurationManager.AppSettings["IdEvento"]);
        }

        public DataTable GetTbFights(string email)
        {
            string strSQL = string.Format(@"select F.EMail, F.Id, F.Iscrizioni, A.FullName from Fights F inner join Atleta A on F.EMail = A.EMail and F.Id = A.Id where F.Email = '{0}' and IdEvento = {1} order by FullName", email, this.IdEvento.ToString());
            DB.DB db = new DB.DB();

            return db.GetTable(strSQL);

        }

        public DataTable GetAllTbFights(string selected)
        {
            string strSQL = string.Empty;

            if (string.Equals(selected, "0"))
                strSQL = string.Format("select C.Nome, F.EMail, F.Id, F.Iscrizioni, A.FullName, A.Weight from Fights F inner join Atleta A on F.EMail = A.EMail and F.Id = A.Id inner join Club C on C.EMail = F.Email where IdEvento = {0} order by Nome, FullName", this.IdEvento.ToString());
            else
            {
                strSQL = string.Format("select C.Nome, F.EMail, F.Id, F.Iscrizioni, A.FullName, A.Weight from Fights F inner join Atleta A on F.EMail = A.EMail and F.Id = A.Id inner join Club C on C.EMail = F.Email where IdEvento = {0} AND SUBSTRING(F.Iscrizioni, {1}, 1) = '1' order by Nome, FullName", this.IdEvento.ToString(), selected);
            }
            DB.DB db = new DB.DB();

            return db.GetTable(strSQL);

        }

        public string Serialize()
        {
            string strSQL = string.Format(@"select * from Fights");
            DB.DB db = new DB.DB();
            DataTable dt = db.GetTable(strSQL);

            dt.TableName = "Fights";

            System.IO.StringWriter writer = new System.IO.StringWriter();

            dt.WriteXml(writer, XmlWriteMode.IgnoreSchema, true);

            return writer.ToString();
        }

        public void PopulateFights(string email)
        {
            SqlDataReader dr = null;
            List<Atleta> atleti = new List<Atleta>();
            Atleta a = null;
            string strSQL = string.Format("select Email, Id from Atleta A where Id not in (select id from Fights where Fights.id = id and IdEvento = {1}) and Email = '{0}'", email, this.IdEvento);
            DB.DB db = new DB.DB();

            dr = db.Reader(strSQL);

            if (dr == null)
                return;

            while (dr.Read())
            {
                a = new Atleta();
                a.EMail = dr["EMail"].ToString();
                a.Id = long.Parse(dr["Id"].ToString());
                atleti.Add(a);
            }
            db.CloseConnection();

            foreach (Atleta at in atleti)
            {
                strSQL = string.Format(@"insert into Fights (EMail, Id, Iscrizioni, IdEvento) values ('{0}', {1}, '0000000000000000', {2})", at.EMail, at.Id.ToString(), this.IdEvento);
                db.ExecuteNonQuery(strSQL);
            }
        }

        public List<Fights> GetFights(string email)
        {
            SqlDataReader dr = null;
            List<Fights> fights = new List<Fights>();
            string strSQL = string.Format(@"select * from Fights where Email = '{0}' and IdEvento = {1} order by Id", email, this.IdEvento);
            DB.DB db = new DB.DB();

            dr = db.Reader(strSQL);

            if (dr == null)
                return fights;

            while (dr.Read())
                fights.Add(new Fights(dr["Email"].ToString(), long.Parse(dr["Id"].ToString()),
                                dr["Iscrizioni"].ToString()));

            db.CloseConnection();

            return fights;
        }

        public bool Update()
        {
            string strSQL = string.Format(@"update Fights set Iscrizioni = '{0}' where EMail = '{1}' and Id = {2} and IdEvento = {3}", this.Iscrizioni, this.EMail, this.Id.ToString(), this.IdEvento.ToString());
            DB.DB db = new DB.DB();
            Boolean saved = db.ExecuteNonQuery(strSQL);

            return saved;
        }

        public bool DeleteFights(string email, long id)
        {
            string strSQL = string.Format(@"delete from Fights where Id = {0} and EMail = '{1}' and IdEvento = {2}", id.ToString(), email, this.IdEvento.ToString());
            DB.DB db = new DB.DB();
            Boolean deleted = db.ExecuteNonQuery(strSQL);

            return deleted;
        }
    }
}